﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using TugasAkhir.Helpers;
using TugasAkhir.Objects;

namespace TugasAkhir.Models
{
    public static class RekomendasiModel
    {
        public static bool GetId(out string id)
        {
            var hasilQuery = new DataSet();
            string query = "SELECT TOP 1 [id_rekomendasi] FROM [dbo].[rekomendasi] ORDER BY [id_rekomendasi] DESC";
            bool flag = SqlServer.ExecuteReader(query, out hasilQuery);

            if (flag)
            {
                if (hasilQuery.Tables[0] != null && hasilQuery.Tables[0].Rows.Count > 0)
                {
                    id = hasilQuery.Tables[0].Rows[0][0].ToString();
                    string idBaru = (int.Parse(id.Substring(1)) + 1).ToString();
                    while (idBaru.Length < 3) idBaru = "0" + idBaru;

                    id = "R" + idBaru;

                    return true;
                }
                else
                {
                    id = "error";
                    return false;
                }
            }
            else
            {
                id = "error";
                return false;
            }
        }

        public static bool GetTabelRekomendasi(out DataSet hasilQuery)
        {
            string query = "SELECT R.[id_rekomendasi], R.[id_pertanyaan], P.[pertanyaan], R.[rekomendasi] FROM [dbo].[rekomendasi] R, [dbo].[pertanyaan] P WHERE R.[id_pertanyaan] = P.[id_pertanyaan] ORDER BY CONVERT(VARCHAR(MAX),P.[pertanyaan]) ASC, P.[id_pertanyaan] ASC";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetTabelRekomendasi(string kataKunci, out DataSet hasilQuery)
        {
            string query = "SELECT R.[id_rekomendasi], R.[id_pertanyaan], P.[pertanyaan], R.[rekomendasi] FROM [dbo].[rekomendasi] R, [dbo].[pertanyaan] P WHERE R.[id_pertanyaan] = P.[id_pertanyaan] AND R.[rekomendasi] LIKE '%" + kataKunci + "%' ORDER BY CONVERT(VARCHAR(MAX),P.[pertanyaan]) ASC, P.[id_pertanyaan] ASC";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetRekomendasi(string idPertanyaan, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT [id_rekomendasi] ,CONVERT(VARCHAR(MAX), [rekomendasi]) AS rekomendasi  FROM [dbo].[rekomendasi] WHERE [id_pertanyaan] = '{0}' ORDER BY [rekomendasi] ASC", idPertanyaan);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetDetailRekomendasi(string id, out RekomendasiObject rekomendasi)
        {
            var hasilQuery = new DataSet();
            string query = String.Format("SELECT [id_rekomendasi] ,[id_pertanyaan], [rekomendasi] FROM [dbo].[rekomendasi] WHERE [id_rekomendasi] = '{0}'", id);

            rekomendasi = new RekomendasiObject();
            bool flag = SqlServer.ExecuteReader(query, out hasilQuery);
            if (flag)
            {
                var data = hasilQuery.Tables[0].Rows[0];
                rekomendasi.IdRekomendasi = data[0].ToString();
                rekomendasi.IdPertanyaan = data[1].ToString();
                rekomendasi.Rekomendasi = data[2].ToString();
            }
            return flag;
        }

        public static bool TambahRekomendasi(RekomendasiObject rekomendasi)
        {
            string query = String.Format("INSERT INTO [dbo].[rekomendasi] VALUES ('{0}' ,'{1}' ,'{2}')", rekomendasi.IdRekomendasi, rekomendasi.IdPertanyaan, rekomendasi.Rekomendasi);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool UbahRekomendasi(RekomendasiObject rekomendasi)
        {
            string query = String.Format("UPDATE [dbo].[rekomendasi] SET [id_pertanyaan] = '{0}' ,[rekomendasi] = '{1}' WHERE [id_rekomendasi] = '{2}'", rekomendasi.IdPertanyaan, rekomendasi.Rekomendasi, rekomendasi.IdRekomendasi);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool HapusRekomendasi(string idRekomendasi)
        {
            string query = String.Format("DELETE FROM [dbo].[rekomendasi] WHERE [id_rekomendasi] = '{0}'", idRekomendasi);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool GetRekomendasi(string idPertanyaan, string tahun, string periode, string idMatakuliah, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT R.[id_rekomendasi], R.[rekomendasi] FROM [dbo].[rekomendasi] R WHERE R.[id_rekomendasi] NOT IN ( SELECT A.[id_rekomendasi] FROM [dbo].[analisis] A WHERE A.[tahun] = '{0}' AND A.[periode] = '{1}' AND A.[id_matakuliah] = '{2}' AND A.[id_pertanyaan] = '{3}' ) AND R.[id_pertanyaan] = '{3}'", tahun, periode, idMatakuliah, idPertanyaan);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetTahunPengambilanDosen(out DataSet hasilQuery)
        {
            string query = "SELECT DISTINCT [tahun] FROM [dbo].[pengambilan_dosen]";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPeriodePengambilanDosen(string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT [periode] FROM [dbo].[pengambilan_dosen] WHERE [tahun] = '{0}'", tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliahPengambilanDosenKajur(string tahun, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT PD.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[pengambilan_dosen] PD, [dbo].[matakuliah] M WHERE PD.[id_matakuliah] = M.[id_matakuliah] AND PD.[tahun] = '{0}' AND PD.[periode] = '{1}'", tahun, periode);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliahPengambilanDosenKalab(string tahun, string periode, string idLab, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT PD.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[pengambilan_dosen] PD, [dbo].[matakuliah] M WHERE PD.[id_matakuliah] = M.[id_matakuliah] AND PD.[tahun] = '{0}' AND PD.[periode] = '{1}' AND M.[id_laboratorium] = '{2}'", tahun, periode, idLab);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliahPengambilanDosenUmum(string tahun, string periode, string idDosen, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT PD.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[pengambilan_dosen] PD, [dbo].[matakuliah] M WHERE PD.[id_matakuliah] = M.[id_matakuliah] AND PD.[tahun] = '{0}' AND PD.[periode] = '{1}' AND PD.[id_dosen] = '{2}'", tahun, periode, idDosen);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }
    }
}